/*
 * DogeDB.java
 *
 * Created on 15 luglio 2007, 12.59
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package dogeDB;

import dogeCore.DogeRecord;
import java.io.ByteArrayInputStream;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author daniele
 */
public abstract class DogeDB {
    
    /** Creates a new instance of DogeDB */
    public DogeDB() {
        username=null;
        password=null;
    }

    /** Does login to the database and initializes local variables */
    public void doLogin(String username, char[] password) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException{
        //saves current username
        this.username=username;
        //reads password from parameter and stores it in a private field
        this.password=convertToStringAndClear(password);
        try {
            //connects to db
            connect();
            //TODO: (?) LOGGING CODE - Login
            //checks if user is a Doge user
            checkValidUser();
            disconnect();
        } catch (SQLException ex){
            //if this is a bad credentials error...
            if (ex.getSQLState().equals("28000")){
                //...resets local fields (they are no more valid)
                this.username=null;
                this.password=null;
            }
            //forwards the exception
            throw ex;
        }

    }

    /** Logs out from database */
    public void doLogout() throws SQLException {
        //disconnects from database
        //TODO: (?) LOGGING CODE - Logout
        if (username!=null){
            disconnect();
        }
        //resets local fields
        this.username=null;
        this.password=null;
        this.department=null;
        this.permissions=null;
    }

    /** returns the permission array for the current user */
    public boolean[] getPermissions() {
        return permissions;
    }
    
    /** returns department for the current user */
    public String getDepartment(){
        return department;
    }

    /** Get document types from database */
    public Vector getDocumentTypes() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        //connect to db
        connect();
        ResultSet rs;
        Vector documentTypes=new Vector();
        Statement documentTypeFetcher=dogeDBConnection.createStatement();
        //fetch document types from database...
        rs=documentTypeFetcher.executeQuery("SELECT type FROM DoGe.document_types");
        //...and copy them in a Vector
        while(rs.next()){
            documentTypes.addElement(rs.getString("type"));
        }
        //close query
        rs.close();
        documentTypeFetcher.close();
        disconnect();
        return documentTypes;
    }

    /** Get communication subjects from database */
    public Vector getSubjects() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //connect to db
        connect();
        ResultSet rs;
        Vector subjects=new Vector();
        Statement subjectFetcher=dogeDBConnection.createStatement();
        //fetch subjects from database...
        rs=subjectFetcher.executeQuery("SELECT name FROM DoGe.communication_subjects");
        //...and copy them in a Vector
        while(rs.next()){
            subjects.addElement(rs.getString("name"));
        }
        //close query
        rs.close();
        subjectFetcher.close();
        disconnect();
        return subjects;
    }

    /** Get users which can create documents from database */
    public Vector getCreators() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //connect to db
        connect();
        ResultSet rs;
        Vector creators=new Vector();
        Statement creatorFetcher=dogeDBConnection.createStatement();
        //fetch creators from database...
        rs=creatorFetcher.executeQuery("SELECT username FROM DoGe.users");
        //...and copy them in a Vector
        while(rs.next()){
            creators.addElement(rs.getString("username"));
        }
        //close query
        rs.close();
        creatorFetcher.close();
        disconnect();
        return creators;
    }

    
    /** connects to database */
    protected abstract void connect() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException;
    /** disconnects from database */
    protected void disconnect() throws SQLException{
        //if connection is not already closed (and is existent), close it
        if ((dogeDBConnection!=null) && (! dogeDBConnection.isClosed())){
            dogeDBConnection.close();
        }
    }

    /** utility method to convert a char[] in a String and clear the char[] array for security reasons*/
    private String convertToStringAndClear(char[] array) {
        //convert char[] in String
        String output=String.copyValueOf(array);
        //blank original char[] array
        for(int i=0;i<array.length;i++){
            array[i]='#';
        }
        //deallocate original array
        array=null;
        //return converted String
        return output;
    }

    /** check if current user is a valid DoGe user and gathers informations about him */
    private void checkValidUser() throws SQLException {
        ResultSet rs;
        Statement userVerifier=dogeDBConnection.createStatement();
        //search in DoGe's users table if the current user is a DoGe user
        rs=userVerifier.executeQuery("SELECT * FROM DoGe.users WHERE username='"+username+"'");   
        //if users exists...
        if (rs.first()){
            //gather user's authorizations and department
            permissions=new boolean[3];
            permissions[0]=rs.getBoolean("insert_auth");
            permissions[1]=rs.getBoolean("edit_auth");
            permissions[2]=rs.getBoolean("search_auth");
            department=rs.getString("department");
            //close query
            rs.close();
            userVerifier.close();
        } else {
            //...else close query and throw exception to notify caller user is not valid
            rs.close();
            userVerifier.close();
            throw new SQLException("User is not a valid DoGe user","28000");
        }
    }

    /** adds a new subject in communication_subjects table */
    public void addNewSubject(String newItem) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        //connect to db
        connect();
        //TODO: (?) LOGGING CODE - New Subject Inserted
        Statement subjectAdder=dogeDBConnection.createStatement();
        //add the new subject in DoGe's communication_subjects table
        subjectAdder.executeUpdate("INSERT INTO DoGe.communication_subjects VALUES ('"+newItem+"')");   
        //close query
        subjectAdder.close();
        disconnect();
    }

    /** Get departments from database */
    public Vector getDepartments() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //connect to db
        connect();
        ResultSet rs;
        Vector departments=new Vector();
        Statement departmentFetcher=dogeDBConnection.createStatement();
        //fetch departments from database...
        rs=departmentFetcher.executeQuery("SELECT name FROM DoGe.departments");
        //...and copy them in a Vector
        while(rs.next()){
            departments.addElement(rs.getString("name"));
        }
        //close query
        rs.close();
        departmentFetcher.close();
        disconnect();
        return departments;        
    }

    /** adds a new document in documents table and returns its protocol number */
    public String insertRecord(DogeRecord record) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        byte[] binaryData=record.getBinaryData();
        ByteArrayInputStream inStream = new ByteArrayInputStream(binaryData);
        int dataSize=binaryData.length;
        //connect to db
        connect();
        //TODO: (?) LOGGING CODE - New Document Inserted
        //prepare query
        int docNumber=getDocumentNumber(record.getFromSubject(),record.getToSubject());
        String insertQuery="INSERT INTO DoGe.documents " +
                "(`doc_num`,`description`,`expires`,`date`,`binary`,`filename`,`from`," +
                "`to`,`created_by`,`inserted_by`,`type`,`department`,`MIME`,`filesize`) VALUES " +
                "("+ docNumber +",'" +record.getDescription()+"',?,?,?,'"+record.getBinaryFileName()+"','"+record.getFromSubject()+"',"+
                "'"+record.getToSubject()+"',?,'"+record.getInsertionOperator()+"','"+record.getDocumentType()+"','"+record.getDepartment()+"'," +
                "'"+record.getBinaryMime()+"',"+dataSize+")";
        PreparedStatement documentAdder=dogeDBConnection.prepareStatement(insertQuery);   
        //set expiration date
        java.util.Date expirationDate=record.getExpirationDate();
        if (expirationDate!=null){
            documentAdder.setDate(1, new Date(expirationDate.getTime()));
        } else {
            documentAdder.setString(1,"0000-00-00");
            //documentAdder.setNull(1,java.sql.Types.DATE);
        }
        //set date
        documentAdder.setDate(2, new Date(record.getDate().getTime()));
        //set binary data
        documentAdder.setBinaryStream(3,inStream,inStream.available());
        //set creator
        String creator=record.getCreator();
        if (creator!=null){
            documentAdder.setString(4,creator);
        } else {
            documentAdder.setNull(4,java.sql.Types.VARCHAR);
        }        
        //execute query to insert document in DoGe's documents table'
        documentAdder.executeUpdate();
        documentAdder.close();
        disconnect();
        //returns protocol nuber of the inserted document
        return record.getFromSubject()+"/"+record.getToSubject()+"/"+docNumber;
    }

    /** get the next (valid) value for doc_num field */
    private int getDocumentNumber(String fromSubject, String toSubject) throws /*ClassNotFoundException, InstantiationException, IllegalAccessException,*/ SQLException {
        //connect to db
        //connect();
        ResultSet rs;
        int nextRecordIndex;
        Statement nextRecordIndexFetcher=dogeDBConnection.createStatement();
        //fetch current index from database...
        rs=nextRecordIndexFetcher.executeQuery("SELECT MAX(doc_num) FROM DoGe.documents WHERE `from`='"+fromSubject+"' AND `to`='"+toSubject+"'");
        if (rs.first()){
            nextRecordIndex=rs.getInt("MAX(doc_num)");
        } else{
            //if there are no previous documents with same from_and_to_subjects this is the first document of that kind
            nextRecordIndex=0;
        }
        //close query
        rs.close();
        nextRecordIndexFetcher.close();
        //disconnect();
        //value for next record will be incremented by 1...
        nextRecordIndex+=1;
        return nextRecordIndex;
    }
    
    //username
    protected String username;
    //password
    protected String password;
    //department to which the current user affers
    private String department;
    //permissions of the current user
    private boolean[] permissions;
    //database connection
    protected Connection dogeDBConnection;
}
